﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIAKADCORE.Helpers
{
    /// <summary>
    /// Kelas SQLHelper
    /// </summary>
    public sealed class SQLHelper
    {
        #region Singleton
        private static SQLHelper instance;

        /// <summary>
        /// Implementasi singleton dari kelas SQLHelper.
        /// </summary>
        public static SQLHelper Instance
        {
            get
            {
                if (instance == null)
                    instance = new SQLHelper();
                return instance;
            }
        }
        #endregion

        #region Fields
        /// <summary>
        /// Field untuk menyimpan connection string yang digunakan.
        /// </summary>
        private string connectionString;
        #endregion

        #region Constructor
        /// <summary>
        /// Konstruktor kelas SQLHelper.
        /// </summary>
        private SQLHelper()
        {
            // Rezki
            //this.connectionString = @"Data Source=localhost;Initial Catalog=integra;Integrated Security=True";
            // Zuh
            this.connectionString = @"Data Source=PANICCODE\REZKIWULAN;Initial Catalog=abet;Integrated Security=True";
        }
        #endregion

        #region Methods
        /// <summary>
        /// Method untuk mengeksekusi SQL syntax non query.
        /// </summary>
        /// <param name="queryString">string query.</param>
        /// <returns>True ketika sukses mengeksekusi dan sebaliknya.</returns>
        public bool ExecuteNonQuery(string queryString)
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                try
                {
                    command.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sqlException)
                {
                    Debug.WriteLine(sqlException.Message);
                    Debug.WriteLine(sqlException.Number);
                    return false;
                }
                catch (Exception exception)
                {
                    Debug.WriteLine(exception.Message);
                    return false;
                }
            }
        }

        /// <summary>
        /// Method untuk mengeksekusi SQL syntax query.
        /// </summary>
        /// <param name="queryString">string query.</param>
        /// <param name="dataset">Hasil query.</param>
        /// <returns>True ketika sukses meng-query dan sebaliknya.</returns>
        public bool ExecuteReader(string queryString, out DataSet dataset)
        {
            dataset = new DataSet();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                try
                {
                    adapter.Fill(dataset);
                    return true;
                }
                catch (SqlException sqlException)
                {
                    Debug.WriteLine(sqlException.Message);
                    Debug.WriteLine(sqlException.Number);
                    return false;
                }
                catch (Exception exception)
                {
                    Debug.WriteLine(exception.Message);
                    return false;
                }
            }
        }

        /// <summary>
        /// Method generik untuk mendapatkan id.
        /// </summary>
        /// <param name="prefix">Prefix dari id.</param>
        /// <param name="length">Panjang maksimum dari id.</param>
        /// <param name="dataset">Dataset yang digunakan untuk mendapatkan id.</param>
        /// <returns></returns>
        public string GetID(string prefix, int length, ref DataSet dataset)
        {
            /* menghindari error ketika tabel kosong */
            if (dataset.Tables[0].Rows.Count == 0)
            {
                string newId = "1";
                while (newId.Length < length - prefix.Length)
                    newId = "0" + newId;
                return prefix + newId;
            }

            string lastId = dataset.Tables[0].Rows[dataset.Tables[0].Rows.Count - 1][0].ToString();
            string newNumber = (int.Parse(lastId.Substring(prefix.Length)) + 1).ToString();
            while (newNumber.Length < length - prefix.Length)
                newNumber = "0" + newNumber;
            return prefix + newNumber;
        }
        #endregion
    }
}
